# !/usr/bin/python3
# coding=utf-8

import pymysql
import pandas as pd

# Open database connection
df = pd.read_excel('示例表格(1).xlsx', index_col=0, header=[0, 1, 2])
df = df.fillna(0)
b = df.columns.values


def insert_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        print('successful')
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def select_mysql(sqll):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sqll)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        if result == ():
            return 'null'
        else:
            new_result = result[0][0]
            return new_result

    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def add_mysql(sqlll):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sqlll)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        a = []
        for i in result:
            a.append(i[0][1:])
        value = int(max(a)) + 1
        return value
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def select_all_quantity(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        result = result[0][0]
        return result
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def exist_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()

        result = result[0][0]
        print(result)
        if result == int(0):
            print('not_exist')
        elif result == int(1):
            print('exist')

    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
        db.close()


import collections

a = []
all = []
all_pd = {}

sql = 'select name from main_table where project="123"'

db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
cursor = db.cursor()
try:
    cursor.execute(sql)
    result = cursor.fetchall()
    print(result)
    for i in result:
        print(i[0])
        all.append(i[0])
        if i[0] in a:
            pass
        else:
            a.append(i[0])
    print(a)
    print(all)
    print(collections.Counter(all))
    max_all = collections.Counter(all)
    print('-----')
    for i in max_all:
        print(i)
        print(max_all[i])
        all_pd[i] = max_all[i]
    print(all_pd)
    print('successful')
except:
    import traceback

    traceback.print_exc()
    print("Error: unable to fetch data")
db.close()
